﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Xml;
using Damar.DataSet;
using E4RPTools;
using Interop.ErpBS800;
using Interop.GcpBE800;
using Interop.StdBE800;

namespace Damar
{
    public class Controller
    {
        private SqlConnection _connection;

        public IWin32Window OwnerWindow { get; set; }

        public string LogFile { get; set; }

        #region Members

        private DateTime? _start, _end;        
        private readonly ErpBS _erp = new ErpBS();
        //private readonly int _hashcode;
        private readonly Result _errors = new Result();
        private int _documentNumber;
        

        #endregion

        #region Events

        public event EventHandler DocumentProcessing;

        #endregion

        #region Properties

        public string DocumentNumber { get { return _documentNumber.ToString(); } }

        public int DocumentCount { get; private set; }

        public int DocumentIndex { get; private set; }

        public Result Errors { get { return _errors; } }

        public DateTime Start { set { _start = value; }}

        public DateTime End { set { _end = value; } }

        #endregion

        #region Private Methods

        //private static string BuildExceptionInfo(Exception exception)
        //{
        //    var textBuffer = new StringBuilder();
        //    textBuffer.AppendFormat(string.Format("DateTime: {0} {1}{2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString(), Environment.NewLine));
        //    textBuffer.AppendFormat(string.Format("Type: {0}{1}", exception.GetType().FullName, Environment.NewLine));
        //    textBuffer.AppendFormat(string.Format("Message: {0}{1}", exception.Message, Environment.NewLine));
        //    textBuffer.AppendFormat(string.Format("Source: {0}{1}", exception.Source, Environment.NewLine));
        //    if (exception.StackTrace != null)
        //        textBuffer.AppendFormat("{0}{1}", exception.StackTrace.Replace("\n", Environment.NewLine), Environment.NewLine);
        //    return textBuffer.ToString();
        //}

        public static void PublishMessage(string filePath, string message)
        {
            var textBuffer = new StringBuilder();
            textBuffer.AppendFormat(string.Format("{0} {1}", Environment.NewLine, message));
            using (var file = File.Open(filePath, FileMode.Append, FileAccess.Write))
            using (var writer = new StreamWriter(file))
                writer.Write(textBuffer.ToString());
        }


        private void PublishAndShowErrorMessage(Exception e, string errorMessage)
        {
            ExceptionPublisher.Publish(LogFile, e);
            ExceptionPublisher.ShowErrorMessage(OwnerWindow, errorMessage, "Damar");
        }

        private void FixConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _connection.Open();
        }

        private ProcessoImportacaoDataSet GetDistinctDocuments()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetDistinctDocuments(sqlTransaction, _start, _end);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de documentos a importar.");
                return new ProcessoImportacaoDataSet();
            }
        }

        private void OnDocumentProcessing()
        {
            var documentProcessing = DocumentProcessing;
            if (documentProcessing != null)
                documentProcessing(this, new EventArgs());
        }

        private string GetDocumentType(string productionType)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var documentType = Database.GetDocumentType(sqlTransaction, productionType);
                if(documentType == "")
                    _errors.Add(_documentNumber.ToString(), "Não existe entrada na tabela de conversão para o tipo de documento " + productionType + " para o tipo Primavera.");
                sqlTransaction.Commit();
                return documentType;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler o tipo de documento.");
                return "";
            }
        }

        private short GetDocumentTypeId(string productionType)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var documentType = Database.GetDocumentType(sqlTransaction, productionType);
                if (documentType == "")
                    _errors.Add(_documentNumber.ToString(), "Não existe entrada na tabela de conversão para o tipo de documento " + productionType + " para o tipo Primavera.");
                var documentTypeId = Database.GetDocumentTypeId(sqlTransaction, documentType);
                if (documentTypeId == -1)
                    _errors.Add(_documentNumber.ToString(), "Não existe entrada na tabela de conversão para o id do tipo de documento " + productionType + " para o tipo Primavera.");
                sqlTransaction.Commit();
                return documentTypeId;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler o tipo de documento.");
                return -1;
            }
        }

        private string GetSeries(string documentType, DateTime date)
        {
            //var query = "select serie from seriesstocks where tipodoc = '" + documentType + "' and serieinactiva = 0 and '" + date.Month + "/" + date.Day + "/" + date.Year +
            //    "' between datainicial and datafinal";
            var query = "select serie from seriesstocks where tipodoc = '" + documentType + "' and serieinactiva = 0 and '" + date.Day + "-" + date.Month+ "-" + date.Year +
                "' between datainicial and datafinal";
            var series = _erp.Consulta(ref query);
            series.Inicio();
            if (series.NoFim())
            {
                _errors.Add(_documentNumber.ToString(), "Não existe série definida para o tipo de documento " + documentType + ".");
                return "";
            }
            return (string)series.Valor(0);
        }

        private int GetDocumentNumber(string documentType, string serie)
        {
            var query = "select numerador + 1 from seriesstocks where tipodoc = '" + documentType + "' and serie = '" + serie + "'";
            try
            {
                var documentNumber = _erp.Consulta(ref query);
                if (documentNumber.Vazia())
                {
                    _errors.Add(_documentNumber.ToString(), "Não existe série definida para o tipo de documento " + documentType + ".");
                    return 0;
                }
                documentNumber.Inicio();
                if (documentNumber.NoFim())
                {
                    _errors.Add(_documentNumber.ToString(), "Não existe série definida para o tipo de documento " + documentType + ".");
                    return 0;
                }
                return (int)documentNumber.Valor(0);
            }
            catch (Exception e)
            {
                return 0;
            }
        }

        private GcpBEDocumentoStock CreateStockDocument(string productionType, DateTime date)
        {
            GcpBEDocumentoStock stockDocument = new GcpBEDocumentoStock();
            var documentType = GetDocumentType(productionType);            
            if (documentType == "")
                return null;
            stockDocument.set_Tipodoc(ref documentType);
            var serie = GetSeries(documentType, date);
            if (serie == "")
                return null;
            stockDocument.set_Serie(ref serie);
            stockDocument.set_DataDoc(ref date);            
            var documentNumber = GetDocumentNumber(documentType, serie);
            stockDocument.set_NumDoc(ref documentNumber);            
            var filial = "000";
            stockDocument.set_Filial(ref filial);
            var modulo = "S";
            stockDocument.set_Modulo(ref modulo);
            var exchange = 1.0;
            stockDocument.set_Cambio(ref exchange);
            stockDocument.set_CambioMBase(ref exchange);
            stockDocument.set_CambioMAlt(ref exchange);
            return stockDocument;
        }

        private bool ExistsClient(string client)
        {
            var query = "select count(*) from clientes where cliente = '" + client + "'";
            var exists = _erp.Consulta(ref query);
            exists.Inicio();
            if (exists.NoFim() || (int)exists.Valor(0) == 0)
            {
                _errors.Add(_documentNumber.ToString(), "O cliente " + client + " não existe em Primavera.");
                return false;
            }
            return (int)exists.Valor(0) > 0;
        }

        private bool StockDocumentUpdated(ImportacaoDataSet.TDU_ImportacaoRow document, GcpBEDocumentoStock stockDocument)
        {
            if (document.IsCDU_NClienteNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("O cliente não está parametrizado (CDU_NCliente) na linha com id {0}.", document.CDU_Id));
                return false;
            }
            if (!ExistsClient(document.CDU_NCliente))
                return false;
            if (!string.IsNullOrEmpty(stockDocument.get_Entidade()) && stockDocument.get_Entidade() != document.CDU_NCliente)
            {
                _errors.Add(_documentNumber.ToString(), string.Format("Existem diferentes clientes para o mesmo documento."));
                return false;
            }
            stockDocument.set_Entidade(document.CDU_NCliente);
            return true;
        }

        private ImportacaoDataSet GetDocumentsToImport(int documentNumber, string documentType)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetDocumentsToImport(sqlTransaction, _start, _end, documentNumber, documentType);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de documentos a importar.");
                return new ImportacaoDataSet();
            }            
        }

        private ArtigosEquivalenciaDataSet GetProducts(string productionProduct, string productionUnit)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetProducts(sqlTransaction, productionProduct, productionUnit);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de produtos de equivalência.");
                return new ArtigosEquivalenciaDataSet();
            }
        }

        private string GetProduct(string productionProduct, string productionUnit)
        {
            var products = GetProducts(productionProduct, productionUnit);
            foreach (var product in products.TDU_ArtigosEquivalencia)
            {
                var query = "select artigo from artigo where artigo = '" + product.CDU_ArtigoPrimavera + "' and unidadebase = '" + product.CDU_UnidadePrimavera + "'";
                var existingProducts = _erp.Consulta(ref query);
                existingProducts.Inicio();
                if (existingProducts.NoFim())
                {
                    _errors.Add(_documentNumber.ToString(), "O artigo " + product.CDU_ArtigoPrimavera + " com a unidade " + product.CDU_UnidadePrimavera + " não existe em Primavera.");
                    return "";
                }
                return (string)existingProducts.Valor(0);
            }
            _errors.Add(_documentNumber.ToString(), "O artigo de produção " + productionProduct + " com a unidade " + productionUnit + " não existe na tabela de equivalências entre artigos.");
            return "";
        }

        private bool ArmazemValido(string armazem)
        {
            var query = "select 1 from Armazens where armazem = '" + armazem + "'";
            var documentNumber = _erp.Consulta(ref query);
            documentNumber.Inicio();
            if (documentNumber.NoFim())
            {
                _errors.Add(_documentNumber.ToString(), "Não existe o armazém definido " + armazem + ".");
                return false;
            }
            return true;
        }

        private bool ProcessesBatchs(string product)
        {
            var query = "select tratamentolotes from artigo where artigo = '" + product + "'";
            var existingProducts = _erp.Consulta(ref query);
            existingProducts.Inicio();
            if (existingProducts.NoFim())
            {
                _errors.Add(_documentNumber.ToString(), "O artigo " + product + " não existe em Primavera.");
                return false;
            }
            return (bool)existingProducts.Valor(0);
        }

        private string SaveBatch(GcpBEArtigoLote batch)
        {
            try
            {
                _erp.IniciaTransaccao();
                _erp.Comercial.ArtigosLotes.Actualiza(ref batch);
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
            finally
            {
                _erp.TerminaTransaccao();
            }
            return string.Empty;
        }

        private void CreateProductBatch(string productName, ImportacaoDataSet.TDU_ImportacaoRow document)
        {
            GcpBEArtigoLote batch;
            if(document.IsCDU_LoteNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("O lote é nulo para o produto {0}.", productName));
                return;
            }
            var batchName = document.CDU_Lote;
            if (_erp.Comercial.ArtigosLotes.Existe(ref productName, ref batchName))
                batch = _erp.Comercial.ArtigosLotes.Edita(ref productName, ref batchName);
            else
            {
                batch = new GcpBEArtigoLote();
                batch.set_Artigo(ref productName);
                batch.set_Lote(ref batchName);
            }
            var active = true;
            batch.set_Activo(ref active);
            if (!document.IsCDU_ControladorNull())
            {
                var controller = document.CDU_Controlador;
                batch.set_Controlador(ref controller);
            }
            if (document.IsCDU_DataFabricoNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("A data de fabrico do produto {0} para o lote {1} é nula.", productName, batchName));
                return;
            }
            var productionDate = document.CDU_DataFabrico;
            productionDate = new DateTime(productionDate.Year, productionDate.Month, productionDate.Day);
            batch.set_DataFabrico(ref productionDate);
            if (document.IsCDU_ValidadeNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("A data de validade do produto {0} para o lote {1} é nula.", productName, batchName));
                return;
            }
            var validToDate = document.CDU_Validade;
            validToDate = new DateTime(validToDate.Year, validToDate.Month, validToDate.Day);
            batch.set_Validade(ref validToDate);
            if (document.IsCDU_DescricaoLoteNull())
            {
                var description = document.CDU_DescricaoLote;
                batch.set_Descricao(ref description);
            }
            var error = SaveBatch(batch);
            if (error != string.Empty)
                _errors.Add(_documentNumber.ToString(), error);            
        }

        private string GetProductName(string product)
        {
            var query = "select descricao from artigo where artigo = '" + product + "'";
            var existingProducts = _erp.Consulta(ref query);
            existingProducts.Inicio();
            if (existingProducts.NoFim())
            {
                _errors.Add(_documentNumber.ToString(), "O artigo " + product + " não existe em Primavera.");
                return "";
            }
            return (string)existingProducts.Valor(0);
        }

        private GcpBELinhaDocumentoStock AddStockDocumentEntry(ImportacaoDataSet.TDU_ImportacaoRow document)
        {
            GcpBELinhaDocumentoStock stockDocumentEntry = new GcpBELinhaDocumentoStock();
            if(document.IsCDU_UnidadeNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("A unidade é nula (CDU_Unidade) na linha com id {0}.", document.CDU_Id));
                return null;
            }
            if (document.IsCDU_ArtigoNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("O artigo é nulo (CDU_Artigo) na linha com id {0}.", document.CDU_Id));
                return null;
            }
            var productName = GetProduct(document.CDU_Artigo, document.CDU_Unidade);
            if (productName == "")
                return null;
            stockDocumentEntry.set_Artigo(ref productName);
            if (document.IsCDU_QuantidadeNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("A quantidade do artigo é nulo (CDU_Quantidade) na linha com id {0}.", document.CDU_Id));
                return null;
            }
            var quantity = Convert.ToDouble(document.CDU_Quantidade);
            stockDocumentEntry.set_Quantidade(ref quantity);
            var date = document.CDU_Data;
            stockDocumentEntry.set_DataStock(ref date);
            stockDocumentEntry.set_Unidade(document.CDU_Unidade);
            if (document.IsCDU_ArmazemNull())
            {
                _errors.Add(_documentNumber.ToString(), string.Format("O armazém do artigo é nulo (CDU_Armazem) na linha com id {0}.", document.CDU_Id));
                return null;
            }
            if (!ArmazemValido(document.CDU_Armazem))
                return null;
            stockDocumentEntry.set_Armazem(document.CDU_Armazem);
            if (ProcessesBatchs(productName))
                CreateProductBatch(productName, document);            
            stockDocumentEntry.set_Lote(document.CDU_Lote);
            stockDocumentEntry.set_Modulo("S");
            stockDocumentEntry.set_EntradaSaida("E");
            var description = GetProductName(productName);
            stockDocumentEntry.set_Descricao(ref description);
            if (!document.IsCDU_PCustoNull())
            {
                var unitPrice = Convert.ToDouble(document.CDU_PCusto);
                stockDocumentEntry.set_PrecUnit(ref unitPrice);
                stockDocumentEntry.set_PCM(ref unitPrice);
            }
            var factorConversao = 1.0;
            var arredondamentoFactorConversao = 2.0;
            stockDocumentEntry.set_FactorConv(ref factorConversao);
            stockDocumentEntry.set_ArredFConv(ref arredondamentoFactorConversao);
            var documentType = GetDocumentTypeId(document.CDU_TipoDoc);
            if (documentType == -1)
                return null;
            stockDocumentEntry.set_TipoDocumento(ref documentType);
            //stockDocumentEntry.set_EstadoBD(enuEstadosBD.estNovo);
            return stockDocumentEntry;
        }

        private ImportacaoDataSet AddStockDocumentEntries(int documentNumber, string documentType, GcpBEDocumentoStock stockDocument)
        {
            var documentsToImport = GetDocumentsToImport(documentNumber, documentType);
            GcpBELinhasDocumentoStock stockLines = new GcpBELinhasDocumentoStock();
            foreach (var document in documentsToImport.TDU_Importacao)
            {
                if(!StockDocumentUpdated(document, stockDocument))
                    continue;
                var stockDocumentEntry = AddStockDocumentEntry(document);
                if (stockDocumentEntry == null)
                    continue;
                stockLines.Insere(stockDocumentEntry);                            
            }
            stockDocument.set_Linhas(stockLines);
            return documentsToImport;
        }

        private static XmlDocument CreateDocumento()
        {
            var document = new XmlDocument();
            document.LoadXml("<Documento />");
            var encoding = document.CreateXmlDeclaration("1.0", "iso-8859-1", "yes");
            document.InsertBefore(encoding, document.DocumentElement);
            return document;
        }

        private static void AddNode(XmlDocument document, string name, params object[] attributes)
        {
            var culture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            var node = document.CreateNode(XmlNodeType.Element, name, "");
            for (var attributeIndex = 0; attributeIndex < attributes.Length; attributeIndex += 2)
            {
                var attribute = document.CreateAttribute(attributes[attributeIndex].ToString());
                var value = attributes[attributeIndex + 1];
                if (value == null)
                    throw new Exception("O campo " + attribute.Name + " não pode ser nulo.");
                if (value.GetType() == typeof(DateTime))
                    attribute.Value = ((DateTime)value).ToShortDateString() + " " + ((DateTime)value).ToShortTimeString();
                else
                    attribute.Value = Convert.ToString(value);
                node.Attributes.Append(attribute);
            }
            if (document.DocumentElement != null)
                document.DocumentElement.AppendChild(node);
            Thread.CurrentThread.CurrentCulture = culture;
        }

        private static string GetCabec(_GcpBEDocumentoStock stockDocument)
        {
            var document = CreateDocumento();
            stockDocument.ID = Guid.NewGuid().ToString();
            AddNode(document, "CD", "ID", stockDocument.ID, "FIL", stockDocument.get_Filial(), "TDO", stockDocument.get_Tipodoc(), "SE", stockDocument.get_Serie(),
                "ND", stockDocument.get_NumDoc(), "CB", stockDocument.get_Cambio(), "CBMB", stockDocument.get_CambioMBase(), "CBMA", stockDocument.get_CambioMAlt(),
                "LA", "0", "LE", "0", "LN", "0", "DT", stockDocument.get_DataDoc(), "DU", stockDocument.get_DataDoc(), "DI", "0", "EI", "0", "ED", "0", "FC", "0", "IF", "0",
                "MO", stockDocument.get_Modulo(), "MD", "EUR", "NV", "0", "TC", "0", "TP", "P", "ET", stockDocument.get_Entidade(), "TE", "C");
            return document.InnerXml;
        }
       
        private static string GetLinhas(_GcpBEDocumentoStock stockDocument)
        {
            var document = CreateDocumento();
            var line = 1;
            foreach (Object entry in stockDocument.get_Linhas())
            {
                if (entry == null)
                    continue;
                var linha = (GcpBELinhaDocumentoStock)entry;
                var idCabecDoc = stockDocument.ID;
                linha.set_IDCabecDoc(ref idCabecDoc);
                var id = Guid.NewGuid().ToString();
                linha.set_IdLinha(ref id);
                linha.set_IdLinhaOrig(ref id);
                AddNode(document, "LD", "TDO", stockDocument.get_Tipodoc(), "ND", stockDocument.get_NumDoc(), "SE", stockDocument.get_Serie(), "FIL", stockDocument.get_Filial(),
                    "AZ", linha.get_Armazem(), "ARR", linha.get_ArredFConv(), "AR", linha.get_Artigo(), "DT", linha.get_DataStock(), "DC", "0", "DS", linha.get_Descricao(),
                    "DP", "0", "DV", "0", "DF", "0", "ED", "0", "ES", linha.get_EntradaSaida(), "EBD", "3", "FC", linha.get_FactorConv(), "ID", linha.get_IdLinha(),
                    "IDCD", linha.get_IDCabecDoc(), "IDOF", "0", "IDLO", linha.get_IdLinhaOrig(), "LO", linha.get_Armazem(), "LT", linha.get_Lote(), "MO", linha.get_Modulo(),
                    "NC", "0", "NL", line, "NLO", "0", "PCM", linha.get_PCM(), "PCMD", "0", "PU", linha.get_PrecUnit(), "QTF", "0", "QT", linha.get_Quantidade(),
                    "TL", "10", "UN", linha.get_Unidade(), "VA", "0", "VB", "0", "VC", "0", "VP", "0", "TDOC", linha.get_TipoDocumento(), "PUNIT", linha.get_PrecUnit(),
                    "APCM", "1", "APCU", "1", "ADUE", "1", "ADUS", "0");
                line++;
            }
            return document.InnerXml;
        }

        private static string GetLinhasNSerie(/*_GcpBEDocumentoStock stockDocument*/)
        {
            var document = CreateDocumento();
            return document.InnerXml;
        }

        private static string GetLinhasConfere(/*_GcpBEDocumentoStock stockDocument*/)
        {
            var document = CreateDocumento();
            return document.InnerXml;
        }

        private void SaveStockDocument(SqlTransaction transaction, _GcpBEDocumentoStock stockDocument)
        {
            try
            {
                var command = _connection.CreateCommand();
                command.Transaction = transaction;
                command.CommandText = "GCP_STK_ActualizaDocumentoStock";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@strXMLCabec", GetCabec(stockDocument));
                command.Parameters.AddWithValue("@strXMLLinhas", GetLinhas(stockDocument));
                command.Parameters.AddWithValue("@strXMLLinhasNSerie", GetLinhasNSerie());
                command.Parameters.AddWithValue("@strXMLLinhasConfere", GetLinhasConfere());
                command.Parameters.AddWithValue("@strModulo", stockDocument.get_Modulo());
                command.Parameters.AddWithValue("@strFilial", stockDocument.get_Filial());
                command.Parameters.AddWithValue("@strSerie", stockDocument.get_Serie());
                command.Parameters.AddWithValue("@strTipoDoc", stockDocument.get_Tipodoc());
                command.Parameters.AddWithValue("@intNumDoc", stockDocument.get_NumDoc());
                command.Parameters.AddWithValue("@datDataDoc", stockDocument.get_DataDoc());
                command.Parameters.AddWithValue("@strArmazemOrigem", "");
                command.Parameters.AddWithValue("@bitDocNovo", 1);
                command.Parameters.AddWithValue("@LicencaDeFilial", 0);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                PublishMessage(LogFile, "[Import] SaveStockDocument error:" + e.Message);
            }            
        }

        private void SaveSeries(SqlTransaction transaction, _GcpBEDocumentoStock stockDocument)
        {
            try
            {
                var command = _connection.CreateCommand();
                command.Transaction = transaction;
                command.CommandText = "UPDATE SeriesStocks SET Numerador=@Numerador, DataUltimoDocumento=@Data WHERE TipoDoc=@TipoDoc AND Serie=@Serie";
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("Numerador", stockDocument.get_NumDoc());
                command.Parameters.AddWithValue("Data", stockDocument.get_DataDoc());
                command.Parameters.AddWithValue("TipoDoc", stockDocument.get_Tipodoc());
                command.Parameters.AddWithValue("Serie", stockDocument.get_Serie());
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                PublishMessage(LogFile, "[Import] SaveSeries error:" + e.Message);
            }  
        }

        private string SaveStockDocument(_GcpBEDocumentoStock stockDocument)
        {
            var transaction = _connection.BeginTransaction();
            try
            {
                SaveStockDocument(transaction, stockDocument);
                if (stockDocument.get_Linhas().NumItens != Database.SavedStockDocumentEntries(transaction, stockDocument.get_Tipodoc(), stockDocument.get_NumDoc()))
                {
                    transaction.Rollback();
                    return "O documento não gravou devido a um erro nas linhas.";
                }
                SaveSeries(transaction, stockDocument);
                transaction.Commit();
            }
            catch (Exception exception)
            {
                transaction.Rollback();
                return exception.Message;
            }

            return string.Empty;
        }

        private void SetImportedDocumentEntries(_GcpBEDocumentoStock stockDocument, ImportacaoDataSet dataSet)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var documentNumber = stockDocument.get_NumDoc();
                var documentType = stockDocument.get_Tipodoc();
                foreach (var document in dataSet.TDU_Importacao)
                    Database.UpdateDocuments(sqlTransaction, document.CDU_NumDoc, document.CDU_TipoDoc, document.CDU_Data, documentNumber, documentType);
                sqlTransaction.Commit();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível actualizar o documento importado.");             
            }
        }

        private bool ExistsErrors()
        {
            return _errors.Errors.Any(error => error.Reference == _documentNumber.ToString());
        }

        #endregion

        public void SetLogFile()
        {
            try
            {
                LogFile = ConfigurationManager.AppSettings["LogFile"];
            }
            catch (Exception)
            {
                ExceptionPublisher.ShowErrorMessage(OwnerWindow, "Não foi possível ler o ficheiro de erros.", "Damar");
            }
        }

        public bool IsLicenseValid()
        {
            try
            {
                var licenseKey = ConfigurationManager.AppSettings["LicenseKey"];
                bool result = E4RPLicense.License.Validate(Product.DamarImport.ToString(), (long)Product.DamarImport, 1, licenseKey);
                return result;
            }
            catch (Exception e)
            {
                PublishAndShowErrorMessage(e, "Aplicação não conseguiu validar a licença.");
                return false;
            }
            
        }

        public bool ServerConnectionOpened()
        {
            var user = ConfigurationManager.AppSettings["ServerUser"];
            var password = ConfigurationManager.AppSettings["ServerPassword"];
            var serverName = ConfigurationManager.AppSettings["ServerName"];
            var databaseName = "Pri" + ConfigurationManager.AppSettings["PrimaveraCompany"];
            var connectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};MultipleActiveResultSets=True;", serverName, databaseName, user, password);            
            try
            {
                _connection = new SqlConnection(connectionString);
                _connection.Open();
            }
            catch (Exception e)
            {
                PublishAndShowErrorMessage(e, "Utilizador sem acesso à base de dados.");
                return false;
            }
            return true;
        }

        public void CloseServerConnection()
        {
            _connection.Close();
        }

        public bool OpenPrimaveraCompany()
        {
            var user = "";
            var company = "";                
            try
            {
                company = ConfigurationManager.AppSettings["PrimaveraCompany"];
                user = ConfigurationManager.AppSettings["PrimaveraUser"];
                var password = ConfigurationManager.AppSettings["PrimaveraPassword"];
                var platform = EnumTipoPlataforma.tpProfissional;
                StdBETransaccao transaction = null;
                var instance = "DEFAULT";
                var primaryMode = true;
                _erp.AbreEmpresaTrabalho(ref platform, ref company, ref user, ref password, ref transaction, ref instance, ref primaryMode);
            }
            catch (Exception e)
            {
                PublishAndShowErrorMessage(e, string.Format("Não foi possível abrir a empresa '{0}', para o utilizador '{1}'.", company, user));
                return false;
            }
            return true;
        }

        public void ClosePrimaveraCompany()
        {            
            _erp.FechaEmpresaTrabalho();
        }

        public bool CanImport()
        {
            var result = new Result();
            if (!IsLicenseValid())
                result.Add("", "Licença inválida.");
            if (!ServerConnectionOpened())
                result.Add("", "Utilizador sem acesso à base de dados.");
            if (_start == null || _end == null)
                result.Add("", "Não existem linhas para importar.");
            if(!result.Failed)
                return true;
            var now = DateTime.Now;
            var fileName = "Import" + now.ToShortDateString() + " " + now.Hour + "-" + now.Minute + ".log";
            var file = new StreamWriter(fileName);
            file.Write(result.Errors);
            file.Close();
            return false;
        }

        public void Import()
        {
            _errors.Reset();
            var documents = GetDistinctDocuments();
            DocumentCount = documents.TDU_Importacao.Count;
            DocumentIndex = 0;
            foreach (var document in documents.TDU_Importacao)
            {
                _documentNumber = document.CDU_NumDoc;
                DocumentIndex++;
                OnDocumentProcessing();
                var stockDocument = CreateStockDocument(document.CDU_TipoDoc, document.CDU_Data);
                if (stockDocument == null)
                    continue;
                var documentEntries = AddStockDocumentEntries(_documentNumber, document.CDU_TipoDoc, stockDocument);
                if (ExistsErrors())
                    continue;
                stockDocument = _erp.Comercial.Stocks.PreencheDadosRelacionados(stockDocument);
                stockDocument.set_DataDoc(document.CDU_Data);                            
                var error = SaveStockDocument(stockDocument);
                if (error != string.Empty)
                {
                    _errors.Add(_documentNumber.ToString(), error.Replace('\r', ' ').Replace('\n', ' '));
                    continue;
                }
                SetImportedDocumentEntries(stockDocument, documentEntries);
            }
        }

        public ImportacaoDataSet GetDocuments()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetDocuments(sqlTransaction, _start, _end);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de documentos.");
                return new ImportacaoDataSet();
            }
        }
    
    }
}